#!/bin/bash
# **********************************************************
# * Author        : zhaoxunfeng
# * Email         : m15675325027@163.com
# * Create time   : 2022-07-27 12:24
# * Filename      : ods_to_dim_init.sh
# * Description   : dim层首日数据装载脚本
# **********************************************************
APP=edu

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [[ -n $2 ]]; then
  do_date=$2
else
  do_date=$(date -d '-1 day' +%F)
fi


dim_user_zip="
insert overwrite table ${APP}.dim_user_zip partition (dt = '9999-12-31')
select data.id,
       data.login_name,
       data.nick_name,
       md5(data.real_name)      name,
       md5(data.phone_num) phone_num,
       md5(data.email)     email,
       data.user_level,
       data.birthday,
       data.gender,
       data.create_time,
       data.operate_time,
       data.status,
       '${do_date}'        start_date,
       '9999-12-31'        end_date
from ${APP}.ods_user_info_inc
where dt = '${do_date}'
  and type = 'bootstrap-insert';
"

dim_video_full="
with video as (
    select id,
           video_name,
           during_sec,
           video_status,
           video_size,
           video_url,
           video_source_id,
           version_id,
           chapter_id,
           course_id,
           publisher_id,
           create_time,
           update_time,
           deleted
    from ${APP}.ods_video_info_full
    where dt = '${do_date}'
),
     chapter as (
         select id,
                chapter_name,
                course_id
         from ${APP}.ods_chapter_info_full
         where dt = '${do_date}'
     )
insert overwrite table ${APP}.dim_video_full partition(dt = '${do_date}')
select video.id,
       video_name,
       during_sec,
       video_status,
       video_size,
       video_source_id,
       version_id,
       video.chapter_id,
       chapter_name,
       video.course_id,
       publisher_id,
       create_time,
       update_time,
       deleted
from video
         left join chapter on video.chapter_id = chapter.id;
"

dim_province_full="
insert overwrite table ${APP}.dim_province_full partition (dt = '${do_date}')
select id,
       name,
       region_id,
       area_code,
       iso_code,
       iso_3166_2
from ${APP}.ods_base_province_full
where dt = '${do_date}';
"

dim_paper_full="
set hive.execution.engine=mr;
with paper as (
    select id,
           paper_title,
           course_id,
           create_time,
           update_time,
           publisher_id,
           deleted
    from ${APP}.ods_test_paper_full
    where dt = '${do_date}'
),
     question as (
         select paper_id,
                collect_list(named_struct('question_id', question_id, 'create_time', create_time, 'deleted', deleted,
                                          'publisher_id', publisher_id)) questions
         from ${APP}.ods_test_paper_question_full
         where dt = '${do_date}'
         group by paper_id
     ),
     course as (
         select id,
                course_name
         from ${APP}.ods_course_info_full
         where dt = '${do_date}'
     )
insert overwrite table ${APP}.dim_paper_full partition (dt='${do_date}')
select paper.id,
       paper_title,
       course_id,
       course.course_name,
       paper.create_time,
       paper.update_time,
       paper.publisher_id,
       questions,
       paper.deleted
from paper
         left join question on paper.id = question.paper_id
         left join course on course.id = paper.course_id;
set hive.execution.engine=spark;
"
dim_course_full="
with course as (
    select id,
           course_name,
           course_slogan,
           course_cover_url,
           subject_id,
           teacher,
           publisher_id,
           chapter_num,
           origin_price,
           reduce_amount,
           actual_price,
           course_introduce,
           create_time,
           deleted,
           update_time
    from ${APP}.ods_course_info_full
    where dt = '${do_date}'
),
     subject as (
         select id, subject_name, category_id
         from ${APP}.ods_base_subject_info_full
         where dt = '${do_date}'
     ),
     category as (
         select id, category_name
         from ${APP}.ods_base_category_info_full
         where dt = '${do_date}'
     )
insert overwrite table ${APP}.dim_course_full partition (dt = '${do_date}')
select course.id,
       course_name,
       course_slogan,
       course_cover_url,
       teacher,
       publisher_id,
       course.subject_id,
       subject_name,
       category_id,
       category.category_name,
       chapter_num,
       origin_price,
       reduce_amount,
       actual_price,
       course_introduce,
       create_time,
       deleted,
       update_time
from course
         left join subject on course.subject_id = subject.id
         left join category on category.id = subject.category_id;
"

case $1 in
"dim_course_full")
  hive -e "${dim_course_full}"
  ;;
"dim_paper_full")
  hive -e "${dim_paper_full}"
  ;;
"dim_province_full")
  hive -e "${dim_province_full}"
  ;;
"dim_user_zip")
  hive -e "${dim_user_zip}"
  ;;
"dim_video_full")
  hive -e "${dim_video_full}"
  ;;
"all")
  hive -e "${dim_course_full}${dim_paper_full}${dim_province_full}${dim_user_zip}${dim_video_full}"
  ;;
esac